/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template path, choose Tools | Templates
 * and open the template in the editor.
 */
package br.gov.cemaden.pcd;

import br.gov.cemaden.pcd.file.CSVFileWriter;
import br.gov.cemaden.pcd.util.HibernateUtil;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.type.DoubleType;
import org.hibernate.type.LongType;
import org.hibernate.type.StringType;

/**
 *
 * @author alan
 */
public class ExportData {
    
    String path;
    Integer year;
    Integer month;
    String from;
    String to;

    public ExportData(String path, Integer year, Integer month, String from, String to) {
        this.path = path;
        this.year = year;
        this.month = month;
        this.from = from;
        this.to = to;
    }

    public void doExport() {
        
        Session session;
        session = HibernateUtil.getSessionFactory().openSession();

        if (session != null) {

            Transaction tx = null;
            List list1 = null;
            List list2 = null;
            List list3 = null;

            try {
                tx = session.beginTransaction();

                list1 = session.createSQLQuery("SELECT DISTINCT m.uf FROM ger_municipio AS m").list();
                session.flush();
                tx.commit();

            } catch (Throwable ex) {
                if (tx != null) {
                    tx.rollback();
                }
                System.err.println(ex);
            }

            for (Iterator i = list1.iterator(); i.hasNext();) {

                List<String[]> data = new LinkedList<String[]>();

                String uf = (String) i.next();

                try {
                    tx = session.beginTransaction();

                    String subquery = "SELECT DISTINCT m.id_municipio"
                            + " FROM pcds.pcds_estacao e INNER JOIN ger_municipio m USING (id_municipio)"
                            + " WHERE e.status = 0 AND e.id_rede = 11 AND m.uf = :uf"
                            + " ORDER BY 1";

                    String query = "SELECT e.codestacao, e.nome, e.latitude, e.longitude, e.id_estacao, m.cidade"
                            + " FROM pcds.pcds_estacao e"
                            + " INNER JOIN ger_municipio m ON e.id_municipio = m.id_municipio"
                            + " WHERE e.id_municipio IN (" + subquery + ")"
                            + " AND e.id_rede = 11"
                            + " AND e.status = 0";

                    list2 = session.createSQLQuery(query)
                            .addScalar("codestacao", StringType.INSTANCE)
                            .addScalar("nome", StringType.INSTANCE)
                            .addScalar("latitude", DoubleType.INSTANCE)
                            .addScalar("longitude", DoubleType.INSTANCE)
                            .addScalar("id_estacao", LongType.INSTANCE)
                            .addScalar("cidade", StringType.INSTANCE)
                            .setParameter("uf", uf)
                            .list();
                    session.flush();
                    tx.commit();

                } catch (Throwable ex) {
                    if (tx != null) {
                        tx.rollback();
                    }
                    System.err.println(ex);
                }

                for (Iterator j = list2.iterator(); j.hasNext();) {

                    Object[] a = (Object[]) j.next();

                    Long idEstacao = (Long) a[4];
                    String codEstacao = (String) a[0];
                    String nomeEstacao = (String) a[1];
                    Double longitude = (Double) a[2];
                    Double latitude = (Double) a[3];
                    String municipio = (String) a[5];

                    try {
                        tx = session.beginTransaction();

                        String sql = "SELECT a.id_aquisicao, a.datahora, m.valor"
                                + " FROM pcds.pcds_aquisicao a"
                                + " INNER JOIN pcds.pcds_medida m ON a.id_aquisicao = m.id_aquisicao AND m.id_sensor = 10"
                                + " WHERE a.datahora BETWEEN '" + from + "' and '" + to + "'"
                                + " AND a.id_estacao = :id_estacao";

                        list3 = session.createSQLQuery(sql)
                                .setParameter("id_estacao", idEstacao)
                                .list();
                        session.flush();
                        tx.commit();

                    } catch (Throwable ex) {
                        if (tx != null) {
                            tx.rollback();
                        }
                        System.err.println(ex);
                    }

                    for (Iterator k = list3.iterator(); k.hasNext();) {

                        Object[] b = (Object[]) k.next();

                        String[] line = {codEstacao, municipio, uf, nomeEstacao, latitude.toString(), longitude.toString(), b[1].toString(), b[2].toString()};

                        data.add(line);
                    }
                }

                String filename = String.format("%s/%s_%d_%d.csv", path, uf, year, month);
                Path file = Paths.get(filename);
                new CSVFileWriter().write(file, data, ';');
            }
            session.close();
        }
        HibernateUtil.getSessionFactory().close();
    }

}
